package com.yang.dao;

import com.yang.entity.NoteEmail;
import org.apache.ibatis.annotations.*;
import org.springframework.stereotype.Repository;

import java.util.List;

/**
 * 短信邮箱成功发送表(NoteEmail)表数据库访问层
 *
 * @author yangdacheng
 * @since 2022-03-02 19:11:02
 */
@Mapper
@Repository
public interface NoteEmailDao {

    /**
     * 添加一个成功发送的邮件/短信
     * @param noteEmail
     * @return
     */
    @Insert("INSERT INTO noteEmail VALUES(" +
            "#{noteEmailId},#{sendTime},#{sendState},#{sendContext},#{userid},#{noteType})")
    Boolean insertNoteEmail(NoteEmail noteEmail);

    /**
     * 查询所有成功发送的邮件/短信
     * @return
     */
    @Results(value = {
            @Result(column = "noteEmailID",property = "noteEmailId",id = true),
            @Result(column = "sendTime",property = "sendTime"),
            @Result(column = "sendState",property = "sendState"),
            @Result(column = "sendContext",property = "sendContext"),
            @Result(column = "userid",property = "userid"),
            @Result(column = "noteType",property = "noteType")
    },id = "noteEmailMapper")
    @Select("SELECT * FROM noteEmail")
    List<NoteEmail> queryAllNoteEmail();

    /**
     * 查询用户下所有成功发送的邮件/短信
     * @param userID
     * @return
     */
    @ResultMap(value = "noteEmailMapper")
    @Select("SELECT * FROM noteEmail WHERE userID = #{userID}")
    List<NoteEmail> queryAllNEByUID(String userID);

    /**
     * 查询邮箱/短信成功发送户的数量
     * @return
     */
    @Select("SELECT COUNT(*) FROM noteEmail WHERE " +
            "sendState = #{state} AND noteType =#{type} AND userID=#{uid}")
    Integer queryEmailCount(Integer state,Integer type,String uid);

    /**
     * 根据用户编号获取7天前的数据
     * @param userID
     * @return
     */
    @Select("SELECT * FROM noteEmail " +
            "where DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= date(sendTime) " +
            "AND userID = #{userID}")
    @ResultMap("noteEmailMapper")
    List<NoteEmail> queryBeforeSevenDay(String userID);

    /**
     * 获取距离多少天前 什么数据类型的数据 发送了多少
     * @param userID
     * @param noteType 发送类型
     * @param beforeDay 距离多少天
     * @return
     */
    @Select("SELECT COUNT(*) FROM noteEmail " +
            "WHERE TO_DAYS( NOW( ) ) - TO_DAYS( sendTime) = #{beforeDay} " +
            "AND noteType = #{noteType} AND userID = #{userID}")
    Integer queryDayCountByState(String userID,Integer noteType,Integer beforeDay);
}